﻿<%@ WebHandler Language="C#" Class="getData" %>

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;

public class getData : IHttpHandler {

    public void ProcessRequest (HttpContext context) {
        var rs = context.Response;
        var rq = context.Request;
        rs.ContentType = "text/plain";

        string ip = "127.0.0.1";
        string usName = "sa";
        string dbpwd = "1105";
        DataTable dta = dat();
        if (dta.Rows.Count > 0)
        {
            ip = dta.Rows[0]["hostIp"].ToString();
            usName = dta.Rows[0]["DbUser"].ToString();
            dbpwd = dta.Rows[0]["DbPwd"].ToString();
        }
        string dbStr = "Data Source=" + ip + ";DataBase=";
        //获取执行次数最多的前N条SQL语句详情
        if (rq["MultiCount"] != null && rq["DbName"] != null)
        {
            int cunt = int.Parse(rq["MultiCount"].Trim());
            string DbName = rq["DbName"].Trim();
            dbStr += DbName+";uid=" + usName + ";pwd=" + dbpwd + "";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            sb.Append("SELECT top " + cunt + " * FROM (SELECT top 1000 creation_time  N'语句编译时间'");
            sb.Append(",DB_NAME(st.dbid) AS dbname,OBJECT_NAME(st.objectid) AS objectname");
            sb.Append(",last_execution_time  LastTime /*最近一次执行时间*/,execution_count/(CASE WHEN DATEDIFF(mi,creation_time,getdate())=0 THEN 1 ELSE DATEDIFF(mi,creation_time,getdate()) END)  as 'count/minute'");
            sb.Append(",datediff(mi,creation_time,getdate()) as 'minute'");
            /*,total_physical_reads N'物理读取总次数'*/
            /*,total_logical_reads/execution_count N'每次逻辑读次数'*/
            /*,total_logical_reads  N'逻辑读取总次数'*/
            /*,total_logical_writes N'逻辑写入总次数'*/
            sb.Append(",execution_count  ExeCount/*执行次数*/,total_worker_time/1000 cpuTtime /*耗费总CPU时间ms*/");
            sb.Append(",total_elapsed_time/1000  totalTime /*总时间ms*/,(total_elapsed_time / execution_count)/1000  avgTime /*平均时间 ms*/");
            sb.Append(",SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,");
            sb.Append("((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)");
            sb.Append(" ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) sqlTxt,st.text ");
            sb.Append("FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ");
            sb.Append("where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,");
            sb.Append("((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)");
            sb.Append(" ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) not like '%fetch%'");
            /*and text not like '%sp_ms%'*/
            /*and qs.execution_count>100 and*/
            /*(total_elapsed_time / execution_count)/1000>150*/
            /*ORDER BY  'count/minute' DESC;*/
            sb.Append("ORDER BY  execution_count DESC) t ORDER BY 'count/minute' DESC");
            string getSql = sb.ToString();
            DataTable dt = SqlHelper.ExecuteDataSet(dbStr,getSql).Tables[0];
            string jsnStr = JsonConvert.SerializeObject(dt);
            rs.Write(jsnStr);
            rs.End();
        }
        //获取执行效率最差的前N条SQL详情
        if (rq["Low"] == "yes" && rq["DbName"] != null)
        {
            string DbName = rq["DbName"].Trim();
            dbStr += DbName+";uid=" + usName + ";pwd=" + dbpwd + "";
            string SqlTxt = @"SELECT
                HIGHEST_CPU_QUERIES.PLAN_HANDLE,
                HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME as TotalTime,
                Q.DBID,Q.OBJECTID,Q.NUMBER, Q.ENCRYPTED,Q.[TEXT] as sqlTxt
            FROM (SELECT TOP 50 QS.PLAN_HANDLE,QS.TOTAL_WORKER_TIME,QS.execution_count
                FROM SYS.DM_EXEC_QUERY_STATS QS
                ORDER BY QS.TOTAL_WORKER_TIME DESC) AS HIGHEST_CPU_QUERIES
                CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS Q
            ORDER BY HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME DESC";

            DataTable dt = SqlHelper.ExecuteDataSet(dbStr,SqlTxt).Tables[0];
            string jsnStr = JsonConvert.SerializeObject(dt);
            rs.Write(jsnStr);
            rs.End();
        }
        //获取SQL语句的执行时间
        if(rq["sqlTxt"] != null && rq["sqlTime"] =="yes" && rq["DbName"] != null)
        {
            string rqSql = rq["sqlTxt"].Trim();
            string DbName = rq["DbName"].Trim();
            dbStr += DbName+";uid=" + usName + ";pwd=" + dbpwd + "";

            rqSql = "DECLARE @d datetime  SET @d=getdate() " + rqSql + " SELECT exeTime=datediff(ms,@d,getdate())";
            DataSet ds = SqlHelper.ExecuteDataSet(dbStr, rqSql);
            DataTable dt = ds.Tables[1];
            if (dt.Rows.Count > 0)
            {
                rs.Write(dt.Rows[0]["exeTime"]);
                rs.End();
            }
        }
    }

    //获取数据库服务器配置参数
    protected DataTable dat()
    {
        DataSet ds = XMLHelper.rds("App_Data/SystemSetting.xml");
        DataTable dtt = ds.Tables[0];
        return dtt;
    }
    public bool IsReusable {
        get {
            return false;
        }
    }

}